package com.totoro.web;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Map.Entry;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.totoro.domain.Project;
import com.totoro.domain.ReviewProject;

public class ReviewProjectExcelView extends AbstractExcelView{

	private final int COLUMNS = 12;
    private final String SHEET_NAME = "ReviewProject";
    private final short DEFAULT_COLUMN_WIDTH = (short)1950;
    private final short DEFAULT_ROW_HEIGHT = (short)500;
    private final String DEFAULT_FONT_NAME = "宋体";
    private final short DEFAULT_FONT_SIZE = (short)12;
    private final int REMARK_AREA_ROWS = 35;
    
    @Override
    protected void buildExcelDocument(Map<String, Object> model,
        HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
        throws Exception {
        
        ReviewProject rp = (ReviewProject)model.get("reviewProject");
        if (rp == null) {
        	rp = new ReviewProject();
        	rp.setAchievementLevel(0);
        	rp.setDocQualityScore(0);
        	rp.setRemark("");
        }
        if(rp.getDocQualityScore()==null || rp.getDocQualityScore()<0){
        	rp.setDocQualityScore(0);
        }
        if(rp.getAchievementLevel()==null || rp.getAchievementLevel()<0){
        	rp.setAchievementLevel(0);
        }
        if(rp.getRemark()==null){
        	rp.setRemark("");
        }
        Project project = (Project)model.get("project");
        
        
        int rowNumber = 0;
        HSSFFont font = workbook.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        font.setFontName(DEFAULT_FONT_NAME);
        font.setFontHeightInPoints((short)14);
        
        HSSFSheet sheet = workbook.createSheet(SHEET_NAME);
//        sheet.setDisplayGridlines(false);
        
        // 合并单元格区域
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, COLUMNS - 1));
        
        for (int i = 0; i < COLUMNS; i++) {
            sheet.setColumnWidth(i, DEFAULT_COLUMN_WIDTH);
        }
        
        HSSFRow titleRow = sheet.createRow(rowNumber);
        titleRow.setHeight((short)600);
        HSSFCell cell = titleRow.createCell(0);
        
        HSSFCellStyle titleStyle = workbook.createCellStyle();
        titleStyle.setAlignment(CellStyle.ALIGN_CENTER);
        titleStyle.setFont(font);
        titleStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        
        // 标题
        cell.setCellValue("山东建筑大学毕业设计（论文）评阅人评审表");
        cell.setCellStyle(titleStyle);
        
        
        HSSFFont cellFont = workbook.createFont();
        cellFont.setFontName(DEFAULT_FONT_NAME);
        cellFont.setFontHeightInPoints(DEFAULT_FONT_SIZE);
        
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        
        cellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        cellStyle.setFont(cellFont);
        cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        
        
        HSSFCellStyle underLineCellStyle = workbook.createCellStyle();
        underLineCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        underLineCellStyle.setFont(cellFont);
        underLineCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        underLineCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        
        // 题目
        rowNumber++;
        HSSFRow subjectRow = sheet.createRow(rowNumber);
       
        
//        int cellWidth = 1;
        
        for (int i = 0; i < COLUMNS - 1; i++) {
            subjectRow.createCell(i);
        }
        
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 2, 9));
        HSSFCell subjectCell = subjectRow.getCell(0);
        subjectRow.setHeight(DEFAULT_ROW_HEIGHT);
        subjectCell.setCellValue("题目");
        subjectCell.setCellStyle(cellStyle);
        
        subjectRow.getCell(2).setCellValue(project.getTitle());
        for (int i = 2; i < COLUMNS - 2; i++) {
        	subjectRow.getCell(i).setCellStyle(underLineCellStyle);
        }
        
        // 班级 
        rowNumber++;
        HSSFRow classRow = sheet.createRow(rowNumber);
        classRow.setHeight(DEFAULT_ROW_HEIGHT);
        for(int i=0;i<COLUMNS;i++){
        	classRow.createCell(i);
        }
        for( int i = 0;i<COLUMNS/2;i++){
        	sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 2*i, 2*i+1));
        }
        HSSFCell classCell = classRow.getCell(0);
        
        classCell.setCellValue("班级:");
        classCell.setCellStyle(cellStyle);
        
        HSSFCell classValueCell = classRow.getCell(2);
        classValueCell.setCellStyle(cellStyle);
        classValueCell.setCellValue(project.getStudent().getStudentClass().getDescription());
        
        // 姓名
        HSSFCell nameCell = classRow.getCell(4);
        nameCell.setCellValue("姓名:");
        nameCell.setCellStyle(cellStyle);
        
        HSSFCell nameValueCell = classRow.getCell(6);
        nameValueCell.setCellValue(project.getStudent().getName());
        nameValueCell.setCellStyle(cellStyle);

        // 学号
        HSSFCell noCell = classRow.getCell(8);
        noCell.setCellValue("学号:");
        noCell.setCellStyle(cellStyle);
        
        HSSFCell noValueCelll = classRow.getCell(10);
        noValueCelll.setCellValue(project.getStudent().getNo());
        noValueCelll.setCellStyle(cellStyle);
        
        rowNumber++;
        sheet.createRow(rowNumber);
        
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("总计", rp.getAchievementLevel()+rp.getDocQualityScore());
        Map<String, Object> map1 = new HashMap<String, Object>();
        map1.put("成果的技术水平（实用性和创新性）（0—10分", rp.getAchievementLevel());
        Map<String, Object> map2 = new HashMap<String, Object>();
        map2.put("设计（论文）质量评分（正确性、条理性、规范性、合理性、清晰、工作量）（0—10分）", rp.getDocQualityScore());
        
        ArrayList<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        list.add(map2);
        list.add(map1);
        list.add(map);
        
        rowNumber = editMarkContentArea(workbook, rowNumber, list);
        
      
        
        //老师签名
        rowNumber = editRemarkArea(workbook, rowNumber, rp.getRemark(),model.get("tutorName").toString());
        
      //表的出处以及注
		HSSFRow tableFromRow = sheet.createRow(41);
		sheet.addMergedRegion(new CellRangeAddress(41, 41, COLUMNS-2, COLUMNS-1));
		Cell tableFromCell = tableFromRow.createCell(10);
		tableFromCell.setCellValue("教务处制");
		
		sheet.addMergedRegion(new CellRangeAddress(43, 43, 0, COLUMNS-1));
		HSSFRow noticeRow = sheet.createRow(43);
		Cell noticeCell = noticeRow.createCell(0);
		noticeCell.setCellValue("注：各专业应根据自己的具体情况，制定出适合本专业的毕业设计（论文）的具体要求和评分标准。");
        
        String excelName = project.getStudent().getNo()+project.getStudent().getName() + "-评阅人评审表.xls";

        response.setHeader("Content-Disposition", "attachment;filename=" 
            + new String(excelName.getBytes(), "ISO-8859-1"));
        
    }

    private int editMarkContentArea(HSSFWorkbook workbook, int rowNumber, java.util.List<Map<String, Object>> list) {
        
        HSSFFont font = workbook.createFont();
        font.setFontName(DEFAULT_FONT_NAME);
        font.setFontHeightInPoints(DEFAULT_FONT_SIZE);
        
        CellStyle leftCellStyle = workbook.createCellStyle();
        leftCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        leftCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        leftCellStyle.setFont(font);
        leftCellStyle.setWrapText(true);
        
        // 设置边框
        leftCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        leftCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        leftCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        
        
        CellStyle rightCellStyle = workbook.createCellStyle();
        rightCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        rightCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        rightCellStyle.setFont(font);
        rightCellStyle.setWrapText(true);
        // 设置边框
       
        rightCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        rightCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        rightCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        
        CellStyle middleCellStyle = workbook.createCellStyle();
        
        middleCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        middleCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        middleCellStyle.setFont(font);
        
        // 设置边框
        middleCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        middleCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        middleCellStyle.setWrapText(true);
     
        CellStyle borderCellStyle = workbook.createCellStyle();
        borderCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        borderCellStyle.setAlignment(CellStyle.ALIGN_CENTER);
        borderCellStyle.setFont(font);
        
        // 设置边框
        borderCellStyle.setBorderTop(CellStyle.BORDER_THIN);
        borderCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        borderCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        borderCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        
        
        
        HSSFSheet sheet = workbook.getSheet(SHEET_NAME);
        HSSFRow row = sheet.createRow(rowNumber);
        row.setHeight((short)(2.3 * DEFAULT_ROW_HEIGHT));
        for(int i = 0;i<COLUMNS;i++){
        	row.createCell(i);
        }
        
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, 3));
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 5, 8));
        for(int i=0;i<2;i++){
        	HashMap<String, Object> m = (HashMap<String, Object>) list.get(i);
        	Entry<String, Object> entry = m.entrySet().iterator().next();
            row.getCell(i*5).setCellValue(entry.getKey());
            row.getCell(i*5+4).setCellValue(entry.getValue().toString());
        }
        HashMap<String, Object> m = (HashMap<String, Object>) list.get(2);
    	Entry<String, Object> entry = m.entrySet().iterator().next();
        row.getCell(10).setCellValue(entry.getKey());
        row.getCell(11).setCellValue(entry.getValue().toString());
       
        for(int i=0;i<COLUMNS;i++){
        	
        	HSSFCell cell = row.getCell(i);
        	switch (i) {
			case 0:;
			case 5:;
			case 10:
				cell.setCellStyle(leftCellStyle);
				break;
			case 3:;
			case 8:
				cell.setCellStyle(rightCellStyle);
				break;
			case 11:
				cell.setCellStyle(borderCellStyle);
				break;
			default:
				cell.setCellStyle(middleCellStyle);
				break;
			}
        }
        return rowNumber++;
    }

    private int editRemarkArea(HSSFWorkbook workbook, int rowNumber, String remark, String turtorNames) {
        
        HSSFFont font = workbook.createFont();
        font.setFontName(DEFAULT_FONT_NAME);
        font.setFontHeightInPoints(DEFAULT_FONT_SIZE);
        
        CellStyle remarkAreaValueCellStyle = workbook.createCellStyle();
        remarkAreaValueCellStyle.setFont(font);
        remarkAreaValueCellStyle.setWrapText(true);
        remarkAreaValueCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        remarkAreaValueCellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        CellStyle remarkAreaTutorCellStyle = workbook.createCellStyle();
        remarkAreaTutorCellStyle.setFont(font);
        remarkAreaTutorCellStyle.setWrapText(true);
        remarkAreaTutorCellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER);
        remarkAreaTutorCellStyle.setAlignment(CellStyle.ALIGN_LEFT);
        
        CellStyle promptCellStyle = workbook.createCellStyle();
        promptCellStyle.setFont(font);
        promptCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        promptCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        
        CellStyle remarkAreaLeftCellStyle = workbook.createCellStyle();
        remarkAreaLeftCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaLeftCellStyle.setFont(font);
        remarkAreaLeftCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        remarkAreaLeftCellStyle.setWrapText(true);
        
        CellStyle remarkAreaBottomCellStyle = workbook.createCellStyle();
        remarkAreaLeftCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaBottomCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        remarkAreaBottomCellStyle.setFont(font);
        
        CellStyle remarkAreaLeftBottomCellStyle = workbook.createCellStyle();
        remarkAreaLeftBottomCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaLeftBottomCellStyle.setFont(font);
        remarkAreaLeftBottomCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        remarkAreaLeftBottomCellStyle.setBorderLeft(CellStyle.BORDER_THIN);
        
        CellStyle remarkAreaRightCellStyle = workbook.createCellStyle();
        remarkAreaRightCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaRightCellStyle.setFont(font);
        remarkAreaRightCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        
        CellStyle remarkAreaRightBottomCellStyle = workbook.createCellStyle();
        remarkAreaRightBottomCellStyle.setVerticalAlignment(CellStyle.VERTICAL_TOP);
        remarkAreaRightBottomCellStyle.setFont(font);
        remarkAreaRightBottomCellStyle.setBorderBottom(CellStyle.BORDER_THIN);
        remarkAreaRightBottomCellStyle.setBorderRight(CellStyle.BORDER_THIN);
        rowNumber++;
        HSSFSheet sheet = workbook.getSheet(SHEET_NAME);
        HSSFRow remarkRow = sheet.createRow(rowNumber);
        HSSFCell remarkCell = remarkRow.createCell(0);
        
        remarkCell.setCellValue("评阅意见:");
        remarkCell.setCellStyle(promptCellStyle);
        
        remarkRow.createCell(COLUMNS - 1).setCellStyle(remarkAreaRightCellStyle);
        
        sheet.addMergedRegion(new CellRangeAddress(rowNumber, rowNumber, 0, COLUMNS - 1));
        
        HSSFRow tmpRow = null;
        for (int i = 1; i < REMARK_AREA_ROWS; i++) {
            tmpRow = sheet.createRow(rowNumber + i);
            tmpRow.createCell(0).setCellStyle(remarkAreaLeftCellStyle);
            tmpRow.createCell(COLUMNS - 1).setCellStyle(remarkAreaRightCellStyle);
            
            if (i == 1) {
                tmpRow.getCell(0).setCellValue("    " + remark);
            }
        }
        
        sheet.addMergedRegion(new CellRangeAddress(rowNumber + 1, rowNumber + REMARK_AREA_ROWS - 5, 0, COLUMNS - 1));
        
        int signatureIndex = COLUMNS-3;
        HSSFCell signatureCell = sheet.getRow(rowNumber + REMARK_AREA_ROWS - 4).createCell(signatureIndex);
        signatureCell.setCellStyle(remarkAreaTutorCellStyle);
        signatureCell.setCellValue("评阅人签字：");
        
        sheet.addMergedRegion(new CellRangeAddress(
            rowNumber + REMARK_AREA_ROWS - 4, rowNumber + REMARK_AREA_ROWS - 4, 
            signatureIndex, COLUMNS - 1));
        
        HSSFCell valueCell = sheet.getRow(rowNumber + REMARK_AREA_ROWS - 3).createCell(COLUMNS-3);
        valueCell.setCellStyle(remarkAreaValueCellStyle);
        valueCell.setCellValue(turtorNames);
        
        sheet.addMergedRegion(new CellRangeAddress(
            rowNumber + REMARK_AREA_ROWS - 3, rowNumber + REMARK_AREA_ROWS - 2, 
            signatureIndex, COLUMNS - 1));
        
        HSSFCell dateCell = sheet.getRow(rowNumber + REMARK_AREA_ROWS - 1).createCell(COLUMNS - 4);
        dateCell.setCellStyle(remarkAreaValueCellStyle);
        
        dateCell.setCellValue(new SimpleDateFormat("yyyy 年 MM 月 dd 日").format(new Date()));
        dateCell.getCellStyle().setAlignment(CellStyle.ALIGN_CENTER);
        sheet.addMergedRegion(new CellRangeAddress(rowNumber + REMARK_AREA_ROWS - 1,
            rowNumber + REMARK_AREA_ROWS - 1, COLUMNS - 4, COLUMNS - 1));
        
        rowNumber += REMARK_AREA_ROWS;
        HSSFCell tmpCell = null;
        tmpRow = sheet.createRow(rowNumber);
        for (int i = 0; i < COLUMNS; i++) {
            tmpCell = tmpRow.createCell(i);
            
            if (i == 0) {
                tmpCell.setCellStyle(remarkAreaLeftBottomCellStyle);
            } else if (i == COLUMNS - 1) {
                tmpCell.setCellStyle(remarkAreaRightBottomCellStyle);
            } else {
                tmpCell.setCellStyle(remarkAreaBottomCellStyle);
            }
        }
        
        return rowNumber;
    }
    
  

}
